﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using PolePositionMobileUtilityGeneral;
using System.Data;

namespace PolePositionMobileUtilityData
{
    public class DImage
    {
        public static DataTable GetAllByInspection(int inspectionId)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _sql.AppendLine("SELECT ");
                _sql.AppendLine("   ImageId, ");
                _sql.AppendLine("   Filename, ");
                _sql.AppendLine("   UtilityInspectionFk, ");
                _sql.AppendLine("   UtilityInspectionResultFk ");
                _sql.AppendLine(" FROM tb_Images ");
                _sql.AppendLine(" WHERE UtilityInspectionFk = @UtilityInspectionFk");
                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, inspectionId));
                _Da.Fill(_Dt);
                return _Dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static int Insert(string fileName, int utilityInspectionFk, int utilityInspectionResultFk)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();

                #region Query
                _sql.AppendLine("Delete tb_Images  ");
                _sql.AppendLine(" where utilityInspectionFk = @utilityInspectionFk");
                #endregion Query

                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@utilityInspectionFk", ParameterDirection.Input, false, utilityInspectionFk));
                //_Da.SelectCommand.ExecuteNonQuery();

                #region Query
                _sql = new StringBuilder();
                _sql.AppendLine("Insert into tb_Images (fileName,utilityInspectionFk) values( ");
                _sql.AppendLine("   @fileName, ");
                _sql.AppendLine("   @utilityInspectionFk) ");
                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@utilityInspectionFk", ParameterDirection.Input, false, utilityInspectionFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@fileName", 255, ParameterDirection.Input, false, fileName));

                _Da.SelectCommand.ExecuteNonQuery();

                _sql = new StringBuilder();
                _sql.AppendLine("Select max(ImageId) from tb_Images");

                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                return (Int32)_Da.SelectCommand.ExecuteScalar();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static void Delete(int ImageId)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _sql.AppendLine("Delete tb_Images ");
                _sql.AppendLine("Where ImageId = @ImageId");

                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@ImageId", ParameterDirection.Input, false, ImageId));

                _Da.SelectCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static void DeleteImageByInspection(int inspectionId)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();

                #region Query
                _sql.AppendLine("Delete tb_Images  ");
                _sql.AppendLine(" where utilityInspectionFk = @utilityInspectionFk");
                #endregion Query

                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@utilityInspectionFk", ParameterDirection.Input, false, inspectionId));
                _Da.SelectCommand.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }
    }
}
